package fr.inserm.u674.chcdbweb.model.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import fr.inserm.u674.chcdbweb.model.DTO.TissueType;

public final class TissueTypeDAO {

	// Constants
	// --------------------------------------------------------------------------------------------

	private static final String SQL_LIST = "SELECT * FROM TissueType";
	private static final String SQL_LIST_WITHOCCURRENCES = 
		"SELECT tissueType.*, COUNT(*) AS nOccurrences FROM TissueType " +
		"LEFT OUTER JOIN Tissue ON (Tissue.tissueTypeID = TissueType.tissueTypeID) " +
		"GROUP BY tissueType.tissueTypeID, tissueType.tissueTypeDescription " +
		"ORDER BY tissueType.tissueTypeID ";
	
	private static final String SQL_EXISTS =
		"SELECT COUNT(*) FROM TissueType WHERE tissueTypeID = ?";

	private static final String SQL_INSERT =
		"INSERT INTO TissueType(tissueTypeID,tissueTypeDescription) VALUES (?,?) ";
	private static final String SQL_UPDATE =
		"UPDATE TissueType SET tissueTypeID = ? , tissueTypeDescription=? "
			+ "WHERE TissueTypeID = ? ";

	// Vars
	// --------------------------------------------------------------------------------------------

	private DAOFactory daoFactory;

	// Constructors
	//

	public TissueTypeDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// Actions
	// --------------------------------------------------------------------------------------------
	public ArrayList<TissueType> list() throws DAOException {
		return list(false);
	}

	public ArrayList<TissueType> list(boolean countOccurrences) throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		ArrayList<TissueType> list = new ArrayList<TissueType>();
		try {
			connection = daoFactory.getConnection();
			if(countOccurrences)
				statement = connection.prepareStatement(SQL_LIST_WITHOCCURRENCES);
			else
				statement = connection.prepareStatement(SQL_LIST);
			resultSet = statement.executeQuery();
			while (resultSet.next())
				list.add(mapTissueType(resultSet, countOccurrences));
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}

		return list;
	}

	public void save(TissueType tissueType) throws DAOException {
		if (exists(tissueType))
			update(tissueType);
		else
			insert(tissueType);
	}

	private void insert(TissueType tissueType) throws DAOException {
		Object[] values = { tissueType.getTissueTypeID(), tissueType.getTissueTypeDescription() };
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			int affectedRows = 0;
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_INSERT, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("No row created in the database for TissueTypeID"
					+ tissueType.getTissueTypeID() + ".");
			}
			else if (affectedRows > 1) {
				throw new DAOException("More than one row created in the database for TissueTypeID"
					+ tissueType.getTissueTypeID() + ".");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}

	}

	private void update(TissueType tissueType) throws DAOException {
		Object[] values =
			{ tissueType.getTissueTypeID(), tissueType.getTissueTypeDescription(),
				tissueType.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;

		try {
			int affectedRows = 0;
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_UPDATE, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new DAOException("No row created in the database for TissueTypeID"
					+ tissueType.getTissueTypeID() + ".");
			}
			else if (affectedRows > 1) {
				throw new DAOException("More than one row created in the database for TissueTypeID"
					+ tissueType.getTissueTypeID() + ".");
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	public boolean exists(TissueType tissueType) throws DAOException {
		Object[] values = { tissueType.getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_EXISTS, false, values);
			resultSet = statement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) > 1)
				throw new DAOException("More than one TissueType for the given TissueTypeID"
					+ tissueType.getTissueTypeID() + ", this should not happen !");
			else if (resultSet.getInt(1) == 1)
				return true;
			else
				return false;
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	private static TissueType mapTissueType(ResultSet resultSet, boolean countOccurrences) throws SQLException {
		
		TissueType tt = new TissueType(resultSet.getString("tissueTypeID"), resultSet
			.getString("tissueTypeDescription"));
		if(countOccurrences)
			tt.setOccurrencesInDB(resultSet.getInt("nOccurrences"));
		
		return tt;
	}

}
